﻿using System.Data;
using System.Data.Common;
using System.Data.SqlClient;
using NPMonitor.Interfaces;
using NPMonitor.Helpers;
using NPMonitor.Configuration;

namespace NPMonitor.Utility
{
    public static class SqlHelper
    {
        internal static string ConnectionString { get; private set; }

        static SqlHelper()
        {
            IEFJobStoreSettingsProvider settingProvider =
            (IEFJobStoreSettingsProvider)Utils.CreateInstanceWithRequiredInterface(ConfigurationSettings.SettingsProvider.JobStoreSettingsProviderType.AssemblyQualifiedName, "IEFJobStoreSettingsProvider");

            ConnectionString = settingProvider.ConnectionString;
        }

        public static void ExecuteStoredProcedure(string spName)
        {
            using (DbConnection conn = new SqlConnection(ConnectionString))
            {
                DbCommand command = conn.CreateCommand();
                command.CommandText = spName;
                command.CommandType = System.Data.CommandType.StoredProcedure;
                command.CommandTimeout = 1800;

                try
                {
                    conn.Open();
                    command.ExecuteNonQuery();
                }
                catch
                {
                    throw;
                }
                finally
                {
                    conn.Close();
                }
            }
        }
        public static void ExecuteNonQuery(string sql)
        {
            using (DbConnection conn = new SqlConnection(ConnectionString))
            {
                DbCommand command = conn.CreateCommand();
                command.CommandText = sql;
                command.CommandType = System.Data.CommandType.Text;
                command.CommandTimeout = 1800;

                try
                {
                    conn.Open();
                    command.ExecuteNonQuery();
                }
                catch
                {
                    throw;
                }
                finally
                {
                    conn.Close();
                }
            }
        }
        public static IDataReader ExecuteReader(string sql)
        {
            DbDataReader dbReader = null;
            using (DbConnection conn = new SqlConnection(ConnectionString))
            {
                DbCommand command = conn.CreateCommand();
                command.CommandText = sql;
                command.CommandType = System.Data.CommandType.Text;
                command.CommandTimeout = 1800;

                try
                {
                    conn.Open();
                    dbReader = command.ExecuteReader();
                }
                catch
                {
                    throw;
                }
                return dbReader;
            }
        }

        public static string ExecuteScalar(string sql)
        {
            return ExecuteScalar(sql, null);
        }

        public static string ExecuteScalar(string sql, SqlParameter[] prams)
        {
            string result;
            using (DbConnection conn = new SqlConnection(ConnectionString))
            {
                DbCommand command = conn.CreateCommand();
                command.CommandText = sql;
                command.CommandType = System.Data.CommandType.Text;
                command.CommandTimeout = 1800;

                if (prams != null && prams.Length > 0)
                {
                    command.Parameters.Clear();
                    foreach (var pram in prams)
                        command.Parameters.Add(pram);
                }
                try
                {
                    conn.Open();
                    result = command.ExecuteScalar().ToString();
                }
                catch
                {
                    throw;
                }
                return result;
            }
        }

        public static void ExecuteNonQuery(string sql, SqlParameter[] prams, int? commandTimeout = null)
        {
            using (var conn = new SqlConnection(ConnectionString))
            {
                var _sql_command = new SqlCommand(sql, conn);
                _sql_command.CommandType = CommandType.Text;

                if (commandTimeout != null)
                {
                    _sql_command.CommandTimeout = commandTimeout.Value;
                }

                if (prams != null && prams.Length > 0)
                {
                    _sql_command.Parameters.Clear();
                    foreach (var pram in prams)
                        _sql_command.Parameters.Add(pram);
                }
                try
                {
                    conn.Open();
                    _sql_command.ExecuteNonQuery();
                }
                catch (SqlException sqlex)
                {
                    throw;
                }
                finally
                {
                    conn.Close();
                }
            }
        }

        public static DataTable ExecuteDataTable(string sql)
        {
            using (var conn = new SqlConnection(ConnectionString))
            {
                SqlDataAdapter sqlDA = new SqlDataAdapter(sql, conn);
                DataTable dt = new DataTable();

                sqlDA.Fill(dt);

                return dt;
            }
        }

        public static DataSet ExecuteDataSet(string sql)
        {
            using (var conn = new SqlConnection(ConnectionString))
            {
                SqlDataAdapter sqlDA = new SqlDataAdapter(sql, conn);
                DataSet ds = new DataSet();

                sqlDA.Fill(ds);

                return ds;
            }
        }
    }
}
